package com.plug.spring;

import com.plug.common.jdbc.DBConnectionManager;
import com.plug.common.jdbc.DBConnectionPool;
import com.plug.common.jdbc.JDBCInfoBean;
import com.plug.common.spring.utils.ApplicationContextHelper;
import com.plug.entity.PlugBackupRecord;
import com.plug.entity.PlugContrast;
import com.plug.entity.PlugDatasource;
import com.plug.service.IPlugBackupRecordService;
import com.plug.service.IPlugContrastService;
import com.plug.service.IPlugDatasourceService;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

/**
 * Created by slaye on 2016/12/19.
 */
public class SpringTask {

    private Logger logger = LoggerFactory.getLogger(getClass());
    private JDBCInfoBean jdbcInfoBean;

    /**
     *
     * @throws InterruptedException
     */
    public void loadDrivers() throws InterruptedException {
    }

    private DBConnectionManager dbConnectionManager = DBConnectionManager.getInstance();

    /**
     * 自动保存100数据方法
     * @throws Exception
     */
    public void saveMethod()throws Exception{
        jdbcInfoBean = new JDBCInfoBean();
        jdbcInfoBean.setName("plug_test");
        jdbcInfoBean.setDriver("com.mysql.jdbc.Driver");
        jdbcInfoBean.setPassword("123456");
        jdbcInfoBean.setUrl("jdbc:mysql://localhost:3306/plug_local?useUnicode=true&characterEncoding=utf8");
        jdbcInfoBean.setUsername("root");
        dbConnectionManager.createPools(jdbcInfoBean);
        Connection conn = dbConnectionManager.getConnection(jdbcInfoBean);
        conn.setAutoCommit(false);
        PreparedStatement ps = conn.prepareStatement("insert into task_test (name,age,sex,address,createtime) values (?,?,?,?,?)");
        SimpleDateFormat sdc = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
        for (int i =0; i< 200;i++){
            ps.setString(1,"task"+i+1+":"+sdc.format(new Date()));
            ps.setLong(2,(i+12));
            ps.setString(3,"男"+i);
            ps.setString(4,"重庆"+i);
            ps.setObject(5, new Date());
            ps.addBatch();
        }
        ps.executeBatch();
        logger.debug("saveMethod新增完成！");
        conn.commit();
        Connection conn1 = dbConnectionManager.getConnection(jdbcInfoBean);
        conn1.setAutoCommit(false);
        PreparedStatement ps_ = conn1.prepareStatement("insert into user_test (name,createtime,strtime,strdate,timestamps) values (?,?,?,?,?)");
        for (int i = 0;i < 200;i++){
            Date date = new Date();
            Calendar calendar = Calendar.getInstance();
            calendar.setTime(date);
            calendar.add(Calendar.HOUR,Integer.valueOf(("-" + i)));
            ps_.setString(1,"张三"+i+1+":"+sdc.format(calendar.getTime()));
            ps_.setLong(2, calendar.getTime().getTime());
            ps_.setString(3, sdc.format(calendar.getTime()));
            ps_.setObject(4, calendar.getTime());
            ps_.setObject(5, calendar.getTime());
            ps_.addBatch();
        }
        ps_.executeBatch();
        conn1.commit();
        Connection conn2 = dbConnectionManager.getConnection(jdbcInfoBean);
        conn2.setAutoCommit(false);
        PreparedStatement ps_1 = conn2.prepareStatement("insert into test (name,adress,age) values (?,?,?)");
        for (int i = 0;i < 200;i++){
            Date date = new Date();
            Calendar calendar = Calendar.getInstance();
            calendar.setTime(date);
            calendar.add(Calendar.HOUR,Integer.valueOf(("-" + i)));
            ps_1.setString(1,"张三"+i+1+":"+sdc.format(calendar.getTime()));
            ps_1.setString(2, "111" + calendar.getTime().getTime());
            ps_1.setInt(3, i+1);
            ps_1.addBatch();
        }
        ps_1.executeBatch();
        conn2.commit();
        dbConnectionManager.freeConnection(jdbcInfoBean.getName(), conn);
        dbConnectionManager.freeConnection(jdbcInfoBean.getName(), conn1);
        dbConnectionManager.freeConnection(jdbcInfoBean.getName(), conn2);
    }


    /**
     *
     * @throws Exception
     */
    public void getMethod()throws Exception{
        IPlugContrastService plugContrastService = (IPlugContrastService) ApplicationContextHelper.getBean("plugContrastService");
        IPlugDatasourceService plugDatasourceService = (IPlugDatasourceService) ApplicationContextHelper.getBean("plugDatasourceService");
        List<PlugContrast> recordList = plugContrastService.findAll();//任务对象集合

        IPlugBackupRecordService plugBackupRecordService= (IPlugBackupRecordService) ApplicationContextHelper.getBean("plugBackupRecordService");
        logger.debug("plugContrastService:{},\t\tplugDatasourceService:{}", plugContrastService, plugDatasourceService);
        List<PlugBackupRecord> plugBackupRecordList = new ArrayList<>(); //任务记录list
        if(recordList != null) {
            for (int i = 0; i < recordList.size(); i++) {
                PlugContrast recordPlug = recordList.get(i);
                PlugDatasource srcPlugDatasource = plugDatasourceService.findByID(recordPlug.getSrcDsId());//获取源数据源
                PlugDatasource tgtPlugDatasource = plugDatasourceService.findByID(recordPlug.getTgtDsId());//获取目标数据源

                PlugBackupRecord plugLoggerRecord=new PlugBackupRecord(); //一个空的任务记录
                plugLoggerRecord.setActionType("备份记录");
                plugLoggerRecord.setSrcDatabase(srcPlugDatasource.getDsName());//任务记录中源数据库名
                plugLoggerRecord.setTgtDatabase(tgtPlugDatasource.getDsName());//任务记录中目标数据库名
                plugLoggerRecord.setSrcTable(recordPlug.getSrcTab());//设置任务记录中的源表
                plugLoggerRecord.setTgtTable(recordPlug.getTgtTab());//设置任务记录中的目标表
                plugLoggerRecord.setPlugContrastId(recordPlug.getId());//设置任务记录中的备份任务配置表ID
                Connection tgtConn = null;
                Connection srcConn = null;
                try {
                    logger.debug("srcPlugDatasource.getDsName():{}",srcPlugDatasource.getDsName());
                    srcConn = dbConnectionManager.getConnection(DBConnectionPool.createJDBCInfoBean(srcPlugDatasource));
                    logger.debug("srcConn.srcConn():{}",srcConn);
                    logger.debug("tgtPlugDatasource.getDsName():{}",tgtPlugDatasource.getDsName());
                    tgtConn = dbConnectionManager.getConnection(DBConnectionPool.createJDBCInfoBean(tgtPlugDatasource));
                    logger.debug("tgtConn.tgtConn():{}",tgtConn);
                    PreparedStatement ps = null;
                    ResultSet rs = null;
                    ps = tgtConn.prepareStatement("select * from " + recordPlug.getTgtTab() + " order by "+recordPlug.getPrimaryKey()+" desc");
                    logger.debug("查询最大ID数:{}", "select * from " + recordPlug.getTgtTab() + " order by "+recordPlug.getPrimaryKey()+" desc");

                    rs = ps.executeQuery();
                    ResultSetMetaData metaData = rs.getMetaData(); // 取得结果集的元元素
                    int colCount = metaData.getColumnCount(); // 取得所有列的个数
                    Long maxIndex = 0L;
                    Long minIndex = 0L;
                    StringBuffer columnName = new StringBuffer();
                    for (int j = 1; j <= colCount; j++) {
                        columnName.append("`" + metaData.getColumnName(j) + "`,");
                    }
                    columnName.deleteCharAt(columnName.length() - 1);


                    while (rs.next()) {
                        maxIndex = rs.getLong(recordPlug.getPrimaryKey());
                        break;
                    }

                    //设置比较起始行
                    int minNumber= 0;

                    rs.close();
                    ps.close();
                    String sql = "";
                    if(maxIndex == null || maxIndex == 0L){
                        ps = srcConn.prepareStatement("select * from " + recordPlug.getSrcTab() + " order by "+recordPlug.getPrimaryKey()+" desc");
                        logger.debug("查询最大ID数:{}", "select * from " + recordPlug.getSrcTab() + " order by "+recordPlug.getPrimaryKey()+" desc");
                        rs = ps.executeQuery();
                        metaData = rs.getMetaData(); // 取得结果集的元元素
                        colCount = metaData.getColumnCount(); // 取得所有列的个数

                        columnName = new StringBuffer();
                        for (int j = 1; j <= colCount; j++) {
                            columnName.append("`" + metaData.getColumnName(j) + "`,");
                        }
                        columnName.deleteCharAt(columnName.length() - 1);

                        while (rs.next()) {
                            maxIndex = rs.getLong(recordPlug.getPrimaryKey());
                            break;
                        }
                        rs.close();
                        ps.close();
                        sql = "select " + columnName.toString() + " from " + recordPlug.getSrcTab() + " where "+recordPlug.getPrimaryKey()+" > " + (maxIndex - 1000L);
                    } else {
                        sql = "select " + columnName.toString() + " from " + recordPlug.getSrcTab() + " where "+recordPlug.getPrimaryKey()+" > " + maxIndex;
                    }
                    ps = srcConn.prepareStatement(sql);
                    logger.debug("根据最大ID数查询未备份数据:{}", sql);
                    rs = ps.executeQuery();
                    metaData = rs.getMetaData(); // 取得结果集的元元素
                    colCount = metaData.getColumnCount(); // 取得所有列的个数
                    StringBuffer sqlColumn = new StringBuffer("insert into " + recordPlug.getTgtTab() + " (");
                    StringBuffer sqlValue = new StringBuffer();



                    for (int j = 1; j <= colCount; j++) {
                        sqlColumn.append("`" + metaData.getColumnName(j) + "`,");
                        sqlValue.append("?,");
                    }
                    sqlColumn.deleteCharAt(sqlColumn.length() - 1);
                    sqlValue.deleteCharAt(sqlValue.length() - 1);
                    sqlColumn.append(") values (" + sqlValue.toString() + ")");
                    tgtConn.setAutoCommit(false);
                    PreparedStatement tgtPs = tgtConn.prepareStatement(sqlColumn.toString());
                    logger.debug("新增备份数据SQL:{}", sqlColumn.toString());
                    while (rs.next()){
                        for (int j = 1; j <= colCount; j++) {
                            Object obj = rs.getObject(j);
                            tgtPs.setObject(j, obj);
                        }
                        minNumber = rs.getInt(recordPlug.getPrimaryKey());
                        tgtPs.addBatch();
                    }
                    tgtPs.executeBatch();
                    tgtConn.commit();
                    tgtPs.close();
                    logger.debug("备份{}数据完成", recordPlug.getTgtTab());
                    //设置备份状态、时间和结果提示信息
                    //设置比较终止行
                    plugLoggerRecord.setToNumber(minNumber);
                    plugLoggerRecord.setFromNumber(Integer.valueOf(maxIndex.toString()));
                    plugLoggerRecord.setState(0);
                    plugLoggerRecord.setMessage("备份"+recordPlug.getTgtTab()+"数据完成");
                }catch (Exception e) {
                    logger.error("任务异常："+e);
                    //设置备份状态和结果提示信息
                    plugLoggerRecord.setToNumber(0);
                    plugLoggerRecord.setFromNumber(0);
                    plugLoggerRecord.setState(1);
                    plugLoggerRecord.setMessage(e.getMessage());
                }finally {
                    if(srcConn != null){
                        dbConnectionManager.freeConnection(srcPlugDatasource.getDsName(), srcConn);
                    }
                    if(tgtConn != null){
                        dbConnectionManager.freeConnection(tgtPlugDatasource.getDsName(), tgtConn);
                    }

                    if(plugLoggerRecord.getFromNumber() == null)
                        plugLoggerRecord.setFromNumber(0);
                    if(plugLoggerRecord.getToNumber() == null)
                        plugLoggerRecord.setToNumber(0);
                    if(plugLoggerRecord.getState() == null)
                        plugLoggerRecord.setState(1);
                    //设置备份时间
                    plugLoggerRecord.setCreateTime(new Date());
                    //提交备份信息
                }
                plugBackupRecordList.add(plugLoggerRecord);
            }
        }
        if(plugBackupRecordList != null && plugBackupRecordList.size() > 0){
            plugBackupRecordService.saveBatch(plugBackupRecordList);
        }
    }

}
